import logging
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter
import openpyxl
import datetime
import os
import sys
import configparser
import ChengBanBuMen
from hanchlogger import logger
import hanchxl

from openpyxl.cell import MergedCell
timeCur = datetime.datetime.now()
numMonth = 4
# dirPath = 'E:\\日常工作\\接诉即办排名情况\\2022\\第三季度\\7月'
dirPath = 'E:\\日常工作\\接诉即办排名情况\\2023\\第二季度\\{}月'.format(numMonth)
# dirPath = 'E:\\日常工作\\11月\\14\\每日必办\\得分统计表\\{}月'.format(numMonth)
# dirPath = os.path.dirname(os.path.realpath(sys.executable))

logFile = dirPath + '\\JieDaoPaiMing.log'
logger.get_log(logFile, 'LoggerJieDaoPaiMing', logging.DEBUG)

iniFilePathCur = dirPath + '\\autoUnresolvedList.ini'
iniConfig = configparser.ConfigParser(allow_no_value=True)
iniConfig.read(iniFilePathCur, encoding='utf-8')

fileJieDan = dirPath + '\\工单明细表-接单.xlsx'
fileKaoHe = dirPath + '\\工单明细表-考核.xlsx'
fileQuanBu = dirPath + '\\全部工单.xlsx'
fileKaoHePiPei = dirPath + '\\工单明细表-考核-已匹配承办部门.xlsx'
fileJieDanPiPei = dirPath + '\\工单明细表-接单-已匹配承办部门.xlsx'
fileJieDanNo = dirPath + '\\工单明细表-接单-未处理.xlsx'
fileChengBanBuMen = dirPath + '\\承办部门.xlsx'
fileResultSave = dirPath + '\\得分统计表.xlsx'
fileColIndexInfo = dirPath + '\\数据列索引信息.xlsx'
fileResultMuBan = dirPath + '\\模板-月度得分统计.xlsx'

# 打开工单明细表-考核.xlsx
bookKaoHe = openpyxl.load_workbook(fileKaoHe, data_only=True)
sheetKaoHe = bookKaoHe.active
# sheetKaoHe = bookKaoHe['考核工单']
maxRowKaoHe = sheetKaoHe.max_row
maxColKaoHe = sheetKaoHe.max_column
logger.debug('工单明细表-考核.xlsx -------行数：{}行    列数：{}列'.format(maxRowKaoHe, maxColKaoHe))

# 打开 工单明细表-接单.xlsx
bookJieDan = openpyxl.load_workbook(fileJieDan, data_only=True)
sheetJieDan = bookJieDan.active
# sheetJieDan = bookJieDan['接单情况']
maxRowJieDan = sheetJieDan.max_row
maxColJieDan = sheetJieDan.max_column
logger.debug('工单明细表-接单.xlsx -------行数：{}行    列数：{}列'.format(maxRowJieDan, maxColJieDan))


# 修改特定的承办部门
hanchxl.xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '疑难工单', '市民诉求处置中心')
hanchxl.xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '疑难案件', '市民诉求处置中心')
hanchxl.xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '应急小分队', '市民诉求处置中心')
hanchxl.xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '流动人口管理组', '社会治理组')
hanchxl.xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '智汇雅苑诺亚方舟物业', '智汇雅苑社区')
hanchxl.xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', '综合城管', '综合行政执法队')

# pi_pei_cheng_ban_jie_dan(sheetJieDan, sheetQuanBu)
# 修改特定的承办部门
hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '疑难工单', '市民诉求处置中心')
hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '疑难案件', '市民诉求处置中心')
hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '应急小分队', '市民诉求处置中心')
hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '流动人口管理组', '社会治理组')
hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '智汇雅苑诺亚方舟物业', '智汇雅苑社区')
hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '综合城管', '综合行政执法队')

# 打开 数据列索引信息.xlsx
bookColIndex = openpyxl.load_workbook(fileColIndexInfo, data_only=True)
sheetColIndexKeShi = bookColIndex['科室']
maxRowColIndexKeShi = sheetColIndexKeShi.max_row
maxColColIndexKeShi = sheetColIndexKeShi.max_column
logger.debug('数据列索引信息.xlsx 科室-------行数：{}行    列数：{}列'.format(maxRowColIndexKeShi, maxColColIndexKeShi))
listColIndexKeshi = {}
for rowIndex in range(2, maxRowColIndexKeShi + 1):
    valueKeShiName = sheetColIndexKeShi.cell(rowIndex, 1).value
    valueKeShiIndex = sheetColIndexKeShi.cell(rowIndex, 2).value
    if valueKeShiName is not None and valueKeShiName != '':
        listColIndexKeshi[valueKeShiName] = valueKeShiIndex
logger.debug('{}'.format(listColIndexKeshi))

sheetColIndexSheQu = bookColIndex['社区村']
maxRowColIndexSheQu = sheetColIndexSheQu.max_row
maxColColIndexSheQu = sheetColIndexSheQu.max_column
logger.debug('数据列索引信息.xlsx 社区村-------行数：{}行    列数：{}列'.format(maxRowColIndexSheQu, maxColColIndexSheQu))
listColIndexSheQu = {}
for rowIndex in range(2, maxRowColIndexSheQu + 1):
    valueSheQuName = sheetColIndexSheQu.cell(rowIndex, 1).value
    valueSheQuIndex = sheetColIndexSheQu.cell(rowIndex, 2).value
    if valueSheQuName is not None and valueSheQuName != '':
        listColIndexSheQu[valueSheQuName] = valueSheQuIndex
logger.debug('{}'.format(listColIndexSheQu))

# 打开 模板-月度得分统计.xlsx
bookResultMuBan = openpyxl.load_workbook(fileResultMuBan, data_only=True)
sheetResultMuBanKeShi = bookResultMuBan['科室']
maxRowResultMuBanKeShi = sheetResultMuBanKeShi.max_row
maxColResultMuBanKeShi = sheetResultMuBanKeShi.max_column
logger.debug('模板-月度得分统计.xlsx 科室-------行数：{}行    列数：{}列'.format(maxRowResultMuBanKeShi, maxColResultMuBanKeShi))

sheetResultMuBanSheQu = bookResultMuBan['科室']
maxRowResultMuBanSheQu = sheetResultMuBanSheQu.max_row
maxColResultMuBanSheQu = sheetResultMuBanSheQu.max_column
logger.debug('模板-月度得分统计.xlsx 社区村-------行数：{}行    列数：{}列'.format(maxRowResultMuBanSheQu, maxColResultMuBanSheQu))

#
# def init_excel_title(sheet_source, str_title, is_she_qu=0):
#     if is_she_qu == 1:
#         sheet_source.merge_cells(range_string='A1:AJ1')
#     else:
#         sheet_source.merge_cells(range_string='A1:AI1')
#
#     sheet_source.cell(1, 1).value = str_title
#     align = Alignment(horizontal='center', vertical='center', wrapText=True)
#     sheet_source.cell(1, 1).alignment = align
#     # 设置行高 第一行 40
#     sheet_source.row_dimensions[1].height = 40
#     sheet_source.row_dimensions[2].height = 25
#     sheet_source.row_dimensions[3].height = 25
#
#     # 序号
#     sheet_source.merge_cells(range_string='A2:A3')
#     sheet_source.cell(2, 1).value = '序号'
#     sheet_source.cell(2, 1).alignment = align
#     sheet_source.column_dimensions[get_column_letter(1)].width = 6
#     # 接单部门
#     sheet_source.merge_cells(range_string='B2:B3')
#     sheet_source.cell(2, 2).value = '接单部门'
#     sheet_source.cell(2, 2).alignment = align
#     sheet_source.column_dimensions[get_column_letter(2)].width = 18
#     # 接单总量
#     strJieDan = '{}2:{}2'.format(get_column_letter(colJieDanInfo), get_column_letter(colJieDanInfo+numJieDanInfoMerge-1))
#     logger.debug('接单情况: {} {}'.format(colJieDanInfo, strJieDan))
#     sheet_source.merge_cells(range_string=strJieDan)
#     sheet_source.cell(2, colJieDanInfo).value = '接单情况'
#     sheet_source.cell(2, colJieDanInfo).alignment = align
#
#     # 接单情况-千人诉求量
#     sheet_source.cell(3, colJieDanInfo).value = '千人诉求量'
#     sheet_source.cell(3, colJieDanInfo).alignment = align
#
#     # 接单情况-接单总量
#     sheet_source.cell(3, colJieDanInfo+1).value = '接单总量'
#     sheet_source.cell(3, colJieDanInfo+1).alignment = align
#
#     # 接单情况-疫情
#     sheet_source.cell(3, colJieDanInfo+2).value = '疫情'
#     sheet_source.cell(3, colJieDanInfo+2).alignment = align
#
#     # 接单情况-日常
#     sheet_source.cell(3, colJieDanInfo+3).value = '日常'
#     sheet_source.cell(3, colJieDanInfo+3).alignment = align
#
#     # 考核总量
#     strNaRuKaoHe = '{}2:{}3'.format(get_column_letter(colCountKaoHe), get_column_letter(colCountKaoHe))
#     sheet_source.merge_cells(range_string=strNaRuKaoHe)
#     logger.debug('考核总量: {} {}'.format(colCountKaoHe, strNaRuKaoHe))
#     sheet_source.cell(2, colCountKaoHe).value = '考核总量'
#     sheet_source.cell(2, colCountKaoHe).alignment = align
#     # 只考核响应率-疫情工单
#     strYiQing = '{}2:{}2'.format(get_column_letter(colYiQing), get_column_letter(colYiQing + numYiQingMerge-1))
#     logger.debug('只考核响应率-疫情工单: {} {}'.format(colYiQing, strYiQing))
#     sheet_source.merge_cells(range_string=strYiQing)
#     sheet_source.cell(2, colYiQing).value = '只考核响应率\r\n疫情工单'
#     sheet_source.cell(2, colYiQing).alignment = align
#     # 只考核响应率-疫情工单-考核(件)
#     sheet_source.cell(3, colYiKaoHe).value = '考核'
#     sheet_source.cell(3, colYiKaoHe).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colYiKaoHe)].width = 6
#     # 只考核响应率-疫情工单-联系(件)
#     sheet_source.cell(3, colYiLian).value = '联系'
#     sheet_source.cell(3, colYiLian).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colYiLian)].width = 6
#     # 只考核响应率-疫情工单-未联系(件)
#     sheet_source.cell(3, colYiLianNo).value = '未联系'
#     sheet_source.cell(3, colYiLianNo).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colYiLianNo)].width = 6
#     # 只考核响应率-疫情工单-响应率(件)
#     sheet_source.cell(3, colYiLvLian).value = '响应率'
#     sheet_source.cell(3, colYiLvLian).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colYiLvLian)].width = 6
#
#     # 只考核响应率-其他工单
#     strYiQing = '{}2:{}2'.format(get_column_letter(colOther), get_column_letter(colOther + numOtherMerge-1))
#     logger.debug('只考核响应率-其他工单: {} {}'.format(colOther, strYiQing))
#     sheet_source.merge_cells(range_string=strYiQing)
#     sheet_source.cell(2, colOther).value = '只考核响应率\r\n其他工单'
#     sheet_source.cell(2, colOther).alignment = align
#     # 只考核响应率-其他工单-考核(件)
#     sheet_source.cell(3, colOtherKaoHe).value = '考核'
#     sheet_source.cell(3, colOtherKaoHe).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colOtherKaoHe)].width = 6
#     # 只考核响应率-其他工单-联系(件)
#     sheet_source.cell(3, colOtherLian).value = '联系'
#     sheet_source.cell(3, colOtherLian).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colOtherLian)].width = 6
#     # 只考核响应率-其他工单-未联系(件)
#     sheet_source.cell(3, colOtherLianNo).value = '未联系'
#     sheet_source.cell(3, colOtherLianNo).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colOtherLianNo)].width = 6
#     # 只考核响应率-其他工单-响应率(件)
#     sheet_source.cell(3, colOtherLvLian).value = '响应率'
#     sheet_source.cell(3, colOtherLvLian).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colOtherLvLian)].width = 6
#
#     # 日常
#     strRiChang = '{}2:{}2'.format(get_column_letter(colRiChang), get_column_letter(colRiChang + numRiChangMerge-1))
#     logger.debug('日常工单: {} {}'.format(colRiChang, strRiChang))
#     sheet_source.merge_cells(range_string=strRiChang)
#     sheet_source.cell(2, colRiChang).value = '考核三率\r\n日常工单'
#     sheet_source.cell(2, colRiChang).alignment = align
#     # 日常-考核(件)
#     sheet_source.cell(3, colRiKaoHe).value = '考核'
#     sheet_source.cell(3, colRiKaoHe).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colRiKaoHe)].width = 6
#     # 日常-联系(件)
#     sheet_source.cell(3, colRiLian).value = '联系'
#     sheet_source.cell(3, colRiLian).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colRiLian)].width = 6
#     # 日常-未联系(件)
#     sheet_source.cell(3, colRiLianNo).value = '未联系'
#     sheet_source.cell(3, colRiLianNo).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colRiLianNo)].width = 6
#     # 日常-解决(件)
#     sheet_source.cell(3, colRiJie).value = '解决'
#     sheet_source.cell(3, colRiJie).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colRiJie)].width = 6
#     # 日常-未解决(件)
#     sheet_source.cell(3, colRiJieNo).value = '未解决'
#     sheet_source.cell(3, colRiJieNo).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colRiJieNo)].width = 6
#     # 日常-满意(件)
#     sheet_source.cell(3, colRiMan).value = '满意'
#     sheet_source.cell(3, colRiMan).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colRiMan)].width = 6
#     # 日常-基本满意(件)
#     sheet_source.cell(3, colRiJiBen).value = '基本满意'
#     sheet_source.cell(3, colRiJiBen).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colRiJiBen)].width = 9
#     # 日常-不满意(件)
#     sheet_source.cell(3, colRiManNo).value = '不满意'
#     sheet_source.cell(3, colRiManNo).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colRiManNo)].width = 6
#     # 日常-响应率(件)
#     sheet_source.cell(3, colRilvLian).value = '响应率'
#     sheet_source.cell(3, colRilvLian).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colRilvLian)].width = 6
#     # 日常-解决率(件)
#     sheet_source.cell(3, colRiLvJie).value = '解决率'
#     sheet_source.cell(3, colRiLvJie).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colRiLvJie)].width = 6
#     # 日常-满意率(件)
#     sheet_source.cell(3, colRiLvMan).value = '满意率'
#     sheet_source.cell(3, colRiLvMan).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colRiLvMan)].width = 6
#
#     # 平均响应率（%）
#     strAvgLian = '{}2:{}3'.format(get_column_letter(colAvgLian), get_column_letter(colAvgLian))
#     logger.debug('日常: {} {}'.format(colDeFenLian, strAvgLian))
#     sheet_source.merge_cells(range_string=strAvgLian)
#     sheet_source.cell(2, colAvgLian).value = '平均\r\n响应率\r\n（%）'
#     sheet_source.cell(2, colAvgLian).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colAvgLian)].width = 9
#     # 平均解决率（%）
#     strAvgJie = '{}2:{}3'.format(get_column_letter(colAvgJie), get_column_letter(colAvgJie))
#     logger.debug('日常: {} {}'.format(colAvgJie, strAvgJie))
#     sheet_source.merge_cells(range_string=strAvgJie)
#     sheet_source.cell(2, colAvgJie).value = '平均\r\n解决率\r\n（%）'
#     sheet_source.cell(2, colAvgJie).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colAvgJie)].width = 9
#     # 平均满意率（%）
#     strAvgMan = '{}2:{}3'.format(get_column_letter(colAvgMan), get_column_letter(colAvgMan))
#     logger.debug('日常: {} {}'.format(colAvgMan, strAvgMan))
#     sheet_source.merge_cells(range_string=strAvgMan)
#     sheet_source.cell(2, colAvgMan).value = '平均\r\n满意率\r\n（%）'
#     sheet_source.cell(2, colAvgMan).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colAvgMan)].width = 9
#
#     # 响应率得分
#     strDeFenLian = '{}2:{}3'.format(get_column_letter(colDeFenLian), get_column_letter(colDeFenLian))
#     logger.debug('日常: {} {}'.format(colDeFenLian, strDeFenLian))
#     sheet_source.merge_cells(range_string=strDeFenLian)
#     sheet_source.cell(2, colDeFenLian).value = '响应率\r\n得分'
#     sheet_source.cell(2, colDeFenLian).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colDeFenLian)].width = 9
#     # 解决率得分
#     strDeFenJie = '{}2:{}3'.format(get_column_letter(colDeFenJie), get_column_letter(colDeFenJie))
#     logger.debug('日常: {} {}'.format(colDeFenJie, strDeFenJie))
#     sheet_source.merge_cells(range_string=strDeFenJie)
#     sheet_source.cell(2, colDeFenJie).value = '解决率\r\n得分'
#     sheet_source.cell(2, colDeFenJie).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colDeFenJie)].width = 9
#     # 满意率得分
#     strDeFenMan = '{}2:{}3'.format(get_column_letter(colDeFenMan), get_column_letter(colDeFenMan))
#     logger.debug('日常: {} {}'.format(colDeFenMan, strDeFenMan))
#     sheet_source.merge_cells(range_string=strDeFenMan)
#     sheet_source.cell(2, colDeFenMan).value = '满意率\r\n得分'
#     sheet_source.cell(2, colDeFenMan).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colDeFenMan)].width = 9
#     # 工单量得分
#     strDeFenGong = '{}2:{}3'.format(get_column_letter(colDeFenGong), get_column_letter(colDeFenGong))
#     logger.debug('日常: {} {}'.format(colDeFenGong, strDeFenGong))
#     sheet_source.merge_cells(range_string=strDeFenGong)
#     sheet_source.cell(2, colDeFenGong).value = '工单量\r\n得分'
#     sheet_source.cell(2, colDeFenGong).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colDeFenGong)].width = 9
#     # 材料报送得分
#     strDeFenCai = '{}2:{}3'.format(get_column_letter(colDeFenCai), get_column_letter(colDeFenCai))
#     logger.debug('日常: {} {}'.format(colDeFenCai, strDeFenCai))
#     sheet_source.merge_cells(range_string=strDeFenCai)
#     sheet_source.cell(2, colDeFenCai).value = '材料报\r\n送得分'
#     sheet_source.cell(2, colDeFenCai).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colDeFenCai)].width = 9
#     # 得分
#     strDeFen = '{}2:{}3'.format(get_column_letter(colDeFen), get_column_letter(colDeFen))
#     logger.debug('日常: {} {}'.format(colDeFen, strDeFen))
#     sheet_source.merge_cells(range_string=strDeFen)
#     sheet_source.cell(2, colDeFen).value = '得分'
#     sheet_source.cell(2, colDeFen).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colDeFen)].width = 6
#     # 排名
#     strPaiMing = '{}2:{}3'.format(get_column_letter(colPaiMing), get_column_letter(colPaiMing))
#     logger.debug('日常: {} {}'.format(colPaiMing, strPaiMing))
#     sheet_source.merge_cells(range_string=strPaiMing)
#     sheet_source.cell(2, colPaiMing).value = '排名'
#     sheet_source.cell(2, colPaiMing).alignment = align
#     sheet_source.column_dimensions[get_column_letter(colPaiMing)].width = 6
#
#     if is_she_qu == 1:
#         # 所属片区
#         strSuoShuPianQu = '{}2:{}3'.format(get_column_letter(colSuoShuPianQu), get_column_letter(colSuoShuPianQu))
#         logger.debug('日常: {} {}'.format(colSuoShuPianQu, strSuoShuPianQu))
#         sheet_source.merge_cells(range_string=strSuoShuPianQu)
#         sheet_source.cell(2, colSuoShuPianQu).value = '所属片区'
#         sheet_source.cell(2, colSuoShuPianQu).alignment = align
#         sheet_source.column_dimensions[get_column_letter(colSuoShuPianQu)].width = 6
#         # 分档
#         strFenDang = '{}2:{}3'.format(get_column_letter(colFenDang), get_column_letter(colFenDang))
#         logger.debug('日常: {} {}'.format(colFenDang, strFenDang))
#         sheet_source.merge_cells(range_string=strFenDang)
#         sheet_source.cell(2, colFenDang).value = '分档'
#         sheet_source.cell(2, colFenDang).alignment = align
#         sheet_source.column_dimensions[get_column_letter(colFenDang)].width = 6
#
#
# sheetKeShi = bookKaoHe.create_sheet(title='科室', index=1)
# sheetSheQu = bookKaoHe.create_sheet(title='社区、村', index=1)
#
# logger.debug('承办部门-.xlsx-------')
# bookChengBabBuMen = openpyxl.load_workbook(fileChengBanBuMen)
# sheetChengBanKeShi = bookChengBabBuMen['科室']
# maxRowChengBanKeShi = sheetChengBanKeShi.max_row
# maxColChengBanKeShi = sheetChengBanKeShi.max_column
# logger.debug('承办部门.xlsx-------科室表 行数：{}行    列数：{}列'.format(maxRowChengBanKeShi, maxColChengBanKeShi))
#
# sheetChengBanSheQu = bookChengBabBuMen['社区村']
# maxRowChengBanSheQu = sheetChengBanSheQu.max_row
# maxColChengBanSheQu = sheetChengBanSheQu.max_column
# logger.debug('承办部门.xlsx-------社区村表 行数：{}行    列数：{}列'.format(maxRowChengBanSheQu, maxColChengBanSheQu))
#
# listKeShi = []
# listSheQu = []
# listKaoHeYi = {}
# listLianXiYi = {}
# listWeiLianXiYi = {}
# listJieJueYi = {}
# listWeiJieJueYi = {}
# listManYiYi = {}
# listJiBenYi = {}
# listBuManYiYi = {}
#
# listKaoHeOther = {}
# listLianXiOther = {}
# listWeiLianXiOther = {}
# lvLianXiOther = {}
#
# listKaoHeRi = {}
# listLianXiRi = {}
# listWeiLianXiRi = {}
# listJieJueRi = {}
# listWeiJieJueRi = {}
# listManYiRi = {}
# listJiBenRi = {}
# listBuManYiRi = {}
#
# lvLianXiRi = {}
# lvWeiLianXiRi = {}
# lvJieJueRi = {}
# lvWeiJieJueRi = {}
# lvManYiRi = {}
# lvJiBenRi = {}
# lvBuManYiRi = {}
# lvManYiFinalRi = {}
# lvLianXiYi = {}
# lvWeiLianXiYi = {}
# lvJieJueYi = {}
# lvWeiJieJueYi = {}
# lvManYiYi = {}
# lvJiBenYi = {}
# lvBuManYiYi = {}
# lvManYiFinalYi = {}
#
# lvLianXiAverage = {}
# lvWeiLianXiAverage = {}
# lvJieJueAverage = {}
# lvWeiJieJueAverage = {}
# lvManYiAverage = {}
# lvJiBenAverage = {}
# lvBuManYiAverage = {}
# lvManYiFinalAverage = {}
#
# deFenLianXi = {}
# deFenManYi = {}
# deFenJieJue = {}
#
# listJieDanRi = {}
# listJieDanYi = {}
#
# deFenGong = {}
# deFenCai = {}
# deFenFinal = {}
#
# logger.debug('考核-疫情：{}'.format(listKaoHeYi))
# logger.debug('考核-日常：{}'.format(listKaoHeRi))
# logger.debug('联系-疫情：{}'.format(listLianXiYi))
# logger.debug('联系-日常：{}'.format(listLianXiRi))
# logger.debug('未联系-疫情：{}'.format(listWeiLianXiYi))
# logger.debug('未联系-日常：{}'.format(listWeiLianXiRi))
# logger.debug('解决-疫情：{}'.format(listJieJueYi))
# logger.debug('解决-日常：{}'.format(listJieJueRi))
# logger.debug('未解决-疫情：{}'.format(listWeiJieJueYi))
# logger.debug('未解决-日常：{}'.format(listWeiJieJueRi))
# logger.debug('满意-疫情：{}'.format(listManYiYi))
# logger.debug('满意-日常：{}'.format(listManYiRi))
# logger.debug('基本满意-疫情：{}'.format(listJiBenYi))
# logger.debug('基本满意-日常：{}'.format(listJiBenRi))
# logger.debug('不满意-疫情：{}'.format(listBuManYiYi))
# logger.debug('不满意-日常：{}'.format(listBuManYiRi))
#
# for rowKeshiList in range(2, maxRowChengBanKeShi+1):
#     valueCellKeShi = sheetChengBanKeShi.cell(rowKeshiList, 1).value
#     if valueCellKeShi is not None:
#         listKeShi.append(valueCellKeShi)
#         listKaoHeYi[valueCellKeShi] = 0
#         listKaoHeRi[valueCellKeShi] = 0
#         listLianXiYi[valueCellKeShi] = 0
#         listLianXiRi[valueCellKeShi] = 0
#         listJieJueYi[valueCellKeShi] = 0
#         listJieJueRi[valueCellKeShi] = 0
#         listManYiYi[valueCellKeShi] = 0
#         listManYiRi[valueCellKeShi] = 0
#         listJiBenYi[valueCellKeShi] = 0
#         listJiBenRi[valueCellKeShi] = 0
#         listWeiLianXiYi[valueCellKeShi] = 0
#         listWeiLianXiRi[valueCellKeShi] = 0
#         listWeiJieJueYi[valueCellKeShi] = 0
#         listWeiJieJueRi[valueCellKeShi] = 0
#         listBuManYiYi[valueCellKeShi] = 0
#         listBuManYiRi[valueCellKeShi] = 0
#         listJieDanRi[valueCellKeShi] = 0
#         listJieDanYi[valueCellKeShi] = 0
#         deFenLianXi[valueCellKeShi] = 0
#         deFenManYi[valueCellKeShi] = 0
#         deFenJieJue[valueCellKeShi] = 0
#         deFenGong[valueCellKeShi] = 0
#         deFenCai[valueCellKeShi] = 0
#         listKaoHeOther[valueCellKeShi] = 0
#         listLianXiOther[valueCellKeShi] = 0
#         listWeiLianXiOther[valueCellKeShi] = 0
#         lvLianXiOther[valueCellKeShi] = None
#         lvLianXiRi[valueCellKeShi] = None
#         lvWeiLianXiRi[valueCellKeShi] = None
#         lvJieJueRi[valueCellKeShi] = None
#         lvWeiJieJueRi[valueCellKeShi] = None
#         lvManYiRi[valueCellKeShi] = None
#         lvJiBenRi[valueCellKeShi] = None
#         lvBuManYiRi[valueCellKeShi] = None
#         lvManYiFinalRi[valueCellKeShi] = None
#         lvManYiFinalRi[valueCellKeShi] = None
#         lvLianXiYi[valueCellKeShi] = None
#         lvWeiLianXiYi[valueCellKeShi] = None
#         lvJieJueYi[valueCellKeShi] = None
#         lvWeiJieJueYi[valueCellKeShi] = None
#         lvManYiYi[valueCellKeShi] = None
#         lvJiBenYi[valueCellKeShi] = None
#         lvBuManYiYi[valueCellKeShi] = None
#         lvManYiFinalYi[valueCellKeShi] = None
#         lvLianXiAverage[valueCellKeShi] = None
#         lvWeiLianXiAverage[valueCellKeShi] = None
#         lvJieJueAverage[valueCellKeShi] = None
#         lvWeiJieJueAverage[valueCellKeShi] = None
#         lvManYiAverage[valueCellKeShi] = None
#         lvJiBenAverage[valueCellKeShi] = None
#         lvBuManYiAverage[valueCellKeShi] = None
#         lvManYiFinalAverage[valueCellKeShi] = None
#         deFenFinal[valueCellKeShi] = None
#
#
# # 社区村的分档情况
# listFenDang = {}
# # 社区村的所属片区
# listSuoShuPianQu = {}
# # 社区村的千人诉求量
# listQianRenSuQiu = {}
#
# colChengBanBuMen = get_col_index(sheetChengBanSheQu, 1, '承办部门')
# colChengBanBuMenFenDang = get_col_index(sheetChengBanSheQu, 1, '分档')
# colChengBanBuMenPianQu = get_col_index(sheetChengBanSheQu, 1, '所属片区')
# colChengBanQianRenSuQiu = get_col_index(sheetChengBanSheQu, 1, '千人诉求量')
# logger.debug('承办部门：{}   分档：{}   所属片区：{}   千人诉求量：{}   '.format(colChengBanBuMen, colChengBanBuMenFenDang
#                                                                              , colChengBanBuMenPianQu
#                                                                              , colChengBanQianRenSuQiu))
# for rowSheQuList in range(2, maxRowChengBanSheQu+1):
#     valueCellSheQu = sheetChengBanSheQu.cell(rowSheQuList, colChengBanBuMen).value
#     if valueCellSheQu is not None:
#         listSheQu.append(valueCellSheQu)
#         listKaoHeYi[valueCellSheQu] = 0
#         listKaoHeRi[valueCellSheQu] = 0
#         listLianXiYi[valueCellSheQu] = 0
#         listLianXiRi[valueCellSheQu] = 0
#         listJieJueYi[valueCellSheQu] = 0
#         listJieJueRi[valueCellSheQu] = 0
#         listManYiYi[valueCellSheQu] = 0
#         listManYiRi[valueCellSheQu] = 0
#         listJiBenYi[valueCellSheQu] = 0
#         listJiBenRi[valueCellSheQu] = 0
#         listWeiLianXiYi[valueCellSheQu] = 0
#         listWeiLianXiRi[valueCellSheQu] = 0
#         listWeiJieJueYi[valueCellSheQu] = 0
#         listWeiJieJueRi[valueCellSheQu] = 0
#         listBuManYiYi[valueCellSheQu] = 0
#         listBuManYiRi[valueCellSheQu] = 0
#         listJieDanRi[valueCellSheQu] = 0
#         listJieDanYi[valueCellSheQu] = 0
#         deFenLianXi[valueCellSheQu] = 0
#         deFenManYi[valueCellSheQu] = 0
#         deFenJieJue[valueCellSheQu] = 0
#         deFenGong[valueCellSheQu] = 0
#         deFenCai[valueCellSheQu] = 0
#         listKaoHeOther[valueCellSheQu] = 0
#         listLianXiOther[valueCellSheQu] = 0
#         listWeiLianXiOther[valueCellSheQu] = 0
#         lvLianXiOther[valueCellSheQu] = None
#         lvLianXiRi[valueCellSheQu] = None
#         lvWeiLianXiRi[valueCellSheQu] = None
#         lvJieJueRi[valueCellSheQu] = None
#         lvWeiJieJueRi[valueCellSheQu] = None
#         lvManYiRi[valueCellSheQu] = None
#         lvJiBenRi[valueCellSheQu] = None
#         lvBuManYiRi[valueCellSheQu] = None
#         lvManYiFinalRi[valueCellSheQu] = None
#         lvLianXiYi[valueCellSheQu] = None
#         lvWeiLianXiYi[valueCellSheQu] = None
#         lvJieJueYi[valueCellSheQu] = None
#         lvWeiJieJueYi[valueCellSheQu] = None
#         lvManYiYi[valueCellSheQu] = None
#         lvJiBenYi[valueCellSheQu] = None
#         lvBuManYiYi[valueCellSheQu] = None
#         lvManYiFinalYi[valueCellSheQu] = None
#         lvLianXiAverage[valueCellSheQu] = None
#         lvWeiLianXiAverage[valueCellSheQu] = None
#         lvJieJueAverage[valueCellSheQu] = None
#         lvWeiJieJueAverage[valueCellSheQu] = None
#         lvManYiAverage[valueCellSheQu] = None
#         lvJiBenAverage[valueCellSheQu] = None
#         lvBuManYiAverage[valueCellSheQu] = None
#         lvManYiFinalAverage[valueCellSheQu] = None
#         deFenFinal[valueCellSheQu] = None
#         listSuoShuPianQu[valueCellSheQu] = sheetChengBanSheQu.cell(rowSheQuList, colChengBanBuMenPianQu).value
#         listFenDang[valueCellSheQu] = sheetChengBanSheQu.cell(rowSheQuList, colChengBanBuMenFenDang).value
#         listQianRenSuQiu[valueCellSheQu] = sheetChengBanSheQu.cell(rowSheQuList, colChengBanQianRenSuQiu).value
#
#
# logger.debug('承办部门-科室：{}'.format(listKeShi))
# logger.debug('承办部门-社区/村：{}'.format(listSheQu))
#
# sheetJieDan.insert_cols(1, 1)
# sheetJieDan.cell(1, 1).value = '是否已处理'
#
# colChengBanJieDan = get_col_index(sheetJieDan, 1, '承办部门')
# colLeiXingJieDan = get_col_index(sheetJieDan, 1, '标签')
#
# maxRowJieDan = sheetJieDan.max_row
# logger.debug('maxRowJieDan ：{}'.format(maxRowJieDan))
# # lineIndexJieDan = 2
# numWeiChuLi = 0
# # while lineIndexJieDan <= maxRowJieDan:
# for lineIndexJieDan in range(2, maxRowJieDan+1):
#     valueChengBanJieDan = sheetJieDan.cell(lineIndexJieDan, colChengBanJieDan).value
#     valueLeiXingJieDan = sheetJieDan.cell(lineIndexJieDan, colLeiXingJieDan).value
#     logger.debug('接单 {} {} {}'.format(lineIndexJieDan, valueChengBanJieDan, valueLeiXingJieDan))
#     if valueChengBanJieDan is not None and valueLeiXingJieDan is not None:
#         valueChengBanJieDan = valueChengBanJieDan.strip()
#         strLeiXing = '{}'.format(valueLeiXingJieDan)
#         if valueChengBanJieDan in listKeShi or valueChengBanJieDan in listSheQu:
#             if strLeiXing.find('1') != -1:
#                 logger.debug('接单  {} {} 疫情'.format(valueChengBanJieDan, valueLeiXingJieDan))
#                 listJieDanYi[valueChengBanJieDan] = listJieDanYi[valueChengBanJieDan] + 1
#                 # sheetJieDan.delete_rows(lineIndexJieDan)
#                 # maxRowJieDan = maxRowJieDan - 1
#                 sheetJieDan.cell(lineIndexJieDan, 1).value = '已处理-疫情'
#             else:
#                 logger.debug('接单  {} {} 日常'.format(valueChengBanJieDan, valueLeiXingJieDan))
#                 listJieDanRi[valueChengBanJieDan] = listJieDanRi[valueChengBanJieDan] + 1
#                 # sheetJieDan.delete_rows(lineIndexJieDan)
#                 # maxRowJieDan = maxRowJieDan - 1
#                 sheetJieDan.cell(lineIndexJieDan, 1).value = '已处理-日常'
#         else:
#             logger.debug('接单  {} {} 不在承办部门列表'.format(valueChengBanJieDan, valueLeiXingJieDan))
#             # lineIndexJieDan = lineIndexJieDan + 1
#             sheetJieDan.cell(lineIndexJieDan, 1).value = '未找到该承办部门'
#             numWeiChuLi = numWeiChuLi + 1
#     else:
#         logger.debug('接单  {} {} 没有承办部门或标签类型'.format(valueChengBanJieDan, valueLeiXingJieDan))
#         # lineIndexJieDan = lineIndexJieDan + 1
#         sheetJieDan.cell(lineIndexJieDan, 1).value = '没有承办部门或标签类型'
#         numWeiChuLi = numWeiChuLi + 1
#
# logger.debug('接单量-疫情：{}'.format(listJieDanYi))
# logger.debug('接单量-日常：{}'.format(listJieDanRi))
# # maxRowJieDan2 = sheetJieDan.max_row
# if numWeiChuLi > 2:
#     # 保存未匹配承办部门的表格
#     bookJieDan.save(fileJieDanNo)
#     logger.debug('保存  {}'.format(fileJieDanNo))
#
# colChengBanIndex = get_col_index(sheetKaoHe, 1, '承办部门')
# colLeiXing = get_col_index(sheetKaoHe, 1, '考核标签')
# colLianXi = get_col_index(sheetKaoHe, 1, '是否联系')
# colJieJue = get_col_index(sheetKaoHe, 1, '是否解决')
# colManYi = get_col_index(sheetKaoHe, 1, '是否满意')
# logger.debug('承办部门：{}列  '.format(colChengBanIndex))
# logger.debug('标签：{}列'.format(colLeiXing))
# logger.debug('联系：{}列'.format(colLianXi))
# logger.debug('解决：{}列'.format(colJieJue))
# logger.debug('满意：{}列'.format(colManYi))
# lineIndex = 2
# numKaoHe = 1
# maxRowKaoHeShow = maxRowKaoHe
# while lineIndex <= maxRowKaoHe:
#     valueChengBanCur = sheetKaoHe.cell(lineIndex, colChengBanIndex).value
#     valueLeiXing = sheetKaoHe.cell(lineIndex, colLeiXing).value
#     valueLianxi = sheetKaoHe.cell(lineIndex, colLianXi).value
#     valueJieJue = sheetKaoHe.cell(lineIndex, colJieJue).value
#     valueManYi = sheetKaoHe.cell(lineIndex, colManYi).value
#     logger.debug('考核 {}/{} {} {} {} {} {}'.format(numKaoHe, maxRowKaoHeShow, valueChengBanCur, valueLeiXing
#                                                     , valueLianxi, valueJieJue, valueManYi))
#     numKaoHe = numKaoHe + 1
#     if valueChengBanCur is not None and valueLeiXing is not None:
#         valueChengBanCur = valueChengBanCur.strip()
#         strLeiXing = '{}'.format(valueLeiXing)
#         if valueChengBanCur in listKeShi or valueChengBanCur in listSheQu:
#             if strLeiXing.find('1') == -1:    # 日常工单
#                 logger.debug(
#                     '考核 {}/{} {} {} {} {} {} 考核三率'.format(numKaoHe, maxRowKaoHeShow, valueChengBanCur, valueLeiXing
#                                                        , valueLianxi, valueJieJue, valueManYi))
#                 if valueLianxi is not None:
#                     valueLianxi = valueLianxi.strip()
#                 if valueJieJue is not None:
#                     valueJieJue = valueJieJue.strip()
#                 if valueManYi is not None:
#                     valueManYi = valueManYi.strip()
#                 listKaoHeRi[valueChengBanCur] = listKaoHeRi[valueChengBanCur] + 1
#                 if valueLianxi == '联系' or valueLianxi == '已联系' or valueLianxi == '是':
#                     listLianXiRi[valueChengBanCur] = listLianXiRi[valueChengBanCur] + 1
#                 if valueLianxi == '未联系' or valueLianxi == '否':
#                     listWeiLianXiRi[valueChengBanCur] = listWeiLianXiRi[valueChengBanCur] + 1
#                 if valueJieJue == '解决' or valueJieJue == '已解决' or valueJieJue == '是':
#                     listJieJueRi[valueChengBanCur] = listJieJueRi[valueChengBanCur] + 1
#                 if valueJieJue == '未解决' or valueJieJue == '否':
#                     listWeiJieJueRi[valueChengBanCur] = listWeiJieJueRi[valueChengBanCur] + 1
#                 if valueManYi == '满意' or valueManYi == '非常满意':
#                     listManYiRi[valueChengBanCur] = listManYiRi[valueChengBanCur] + 1
#                 if valueManYi == '基本满意':
#                     listJiBenRi[valueChengBanCur] = listJiBenRi[valueChengBanCur] + 1
#                 if valueManYi == '不满意' or valueManYi == '非常不满意':
#                     listBuManYiRi[valueChengBanCur] = listBuManYiRi[valueChengBanCur] + 1
#                 sheetKaoHe.delete_rows(lineIndex)
#                 maxRowQuanBu = maxRowKaoHe - 1
#             elif strLeiXing == '1001':
#                 logger.debug('考核 {}/{} {} {} {} {} {} '
#                     '仅考核响应率-其他'.format(numKaoHe, maxRowKaoHeShow, valueChengBanCur, valueLeiXing
#                                                        , valueLianxi, valueJieJue, valueManYi))
#                 listKaoHeOther[valueChengBanCur] = listKaoHeOther[valueChengBanCur] + 1
#                 if valueLianxi == '联系' or valueLianxi == '已联系' or valueLianxi == '是':
#                     listLianXiOther[valueChengBanCur] = listLianXiOther[valueChengBanCur] + 1
#                 if valueLianxi == '未联系' or valueLianxi == '否':
#                     listWeiLianXiOther[valueChengBanCur] = listWeiLianXiOther[valueChengBanCur] + 1
#                 sheetKaoHe.delete_rows(lineIndex)
#                 maxRowQuanBu = maxRowKaoHe - 1
#             else:
#                 logger.debug('考核 {}/{} {} {} {} {} {} '
#                              '仅考核响应率-疫情'.format(numKaoHe, maxRowKaoHeShow, valueChengBanCur, valueLeiXing
#                                                         , valueLianxi, valueJieJue, valueManYi))
#                 listKaoHeYi[valueChengBanCur] = listKaoHeYi[valueChengBanCur] + 1
#                 if valueLianxi == '联系' or valueLianxi == '已联系' or valueLianxi == '是':
#                     listLianXiYi[valueChengBanCur] = listLianXiYi[valueChengBanCur] + 1
#                 if valueLianxi == '未联系' or valueLianxi == '否':
#                     listWeiLianXiYi[valueChengBanCur] = listWeiLianXiYi[valueChengBanCur] + 1
#                 sheetKaoHe.delete_rows(lineIndex)
#                 maxRowQuanBu = maxRowKaoHe - 1
#             logger.debug('考核 {}/{} {} '
#                          '考核三率：{} '
#                          '响应率-其他：{} '
#                          '响应率-疫情：{}'.format(numKaoHe, maxRowKaoHeShow, valueChengBanCur
#                                                        , listKaoHeRi[valueChengBanCur], listKaoHeOther[valueChengBanCur]
#                                                        , listKaoHeYi[valueChengBanCur]))
#         else:
#             lineIndex = lineIndex + 1
#     else:
#         lineIndex = lineIndex + 1
#
# logger.debug('考核-疫情：{}'.format(listKaoHeYi))
# logger.debug('考核-日常：{}'.format(listKaoHeRi))
# logger.debug('联系-疫情：{}'.format(listLianXiYi))
# logger.debug('联系-日常：{}'.format(listLianXiRi))
# logger.debug('未联系-疫情：{}'.format(listWeiLianXiYi))
# logger.debug('未联系-日常：{}'.format(listWeiLianXiRi))
# logger.debug('解决-疫情：{}'.format(listJieJueYi))
# logger.debug('解决-日常：{}'.format(listJieJueRi))
# logger.debug('未解决-疫情：{}'.format(listWeiJieJueYi))
# logger.debug('未解决-日常：{}'.format(listWeiJieJueRi))
# logger.debug('满意-疫情：{}'.format(listManYiYi))
# logger.debug('满意-日常：{}'.format(listManYiRi))
# logger.debug('基本满意-疫情：{}'.format(listJiBenYi))
# logger.debug('基本满意-日常：{}'.format(listJiBenRi))
# logger.debug('不满意-疫情：{}'.format(listBuManYiYi))
# logger.debug('不满意-日常：{}'.format(listBuManYiRi))
#
# countJieDanRi = 0
# countJieDanYi = 0
# countKaoHeRi = 0
# countKaoHeYi = 0
# countKaoHeOther = 0
# countLianXiRi = 0
# countLianXiYi = 0
# countLianXiOther = 0
# countWeiLianXiRi = 0
# countWeiLianXiYi = 0
# countWeiLianXiOther = 0
# countJieJueRi = 0
# countJieJueYi = 0
# countWeiJieJueRi = 0
# countWeiJieJueYi = 0
# countManYiRi = 0
# countManYiYi = 0
# countJiBenRi = 0
# countJiBenYi = 0
# countBuManYiRi = 0
# countBuManYiYi = 0
# countKaoHeRiKeShi = 0
# countKaoHeYiKeShi = 0
# countKaoHeOtherKeShi = 0
# countKaoHeRiSheQu = 0
# countKaoHeYiSheQu = 0
# countKaoHeOtherSheQu = 0
#
# for indexTongJi in range(0, len(listKeShi)):
#     nameTongJi = listKeShi[indexTongJi]
#     countJieDanRi = countJieDanRi + listJieDanRi[nameTongJi]
#     countJieDanYi = countJieDanYi + listJieDanYi[nameTongJi]
#     countKaoHeRi = countKaoHeRi + listKaoHeRi[nameTongJi]
#     countKaoHeRiKeShi = countKaoHeRiKeShi + listKaoHeRi[nameTongJi]
#     countKaoHeYi = countKaoHeYi + listKaoHeYi[nameTongJi]
#     countKaoHeYiKeShi = countKaoHeYiKeShi + listKaoHeYi[nameTongJi]
#     countLianXiRi = countLianXiRi + listLianXiRi[nameTongJi]
#     countLianXiYi = countLianXiYi + listLianXiYi[nameTongJi]
#     countWeiLianXiRi = countWeiLianXiRi + listWeiLianXiRi[nameTongJi]
#     countWeiLianXiYi = countWeiLianXiYi + listWeiLianXiYi[nameTongJi]
#     countJieJueRi = countJieJueRi + listJieJueRi[nameTongJi]
#     countJieJueYi = countJieJueYi + listJieJueYi[nameTongJi]
#     countWeiJieJueRi = countWeiJieJueRi + listWeiJieJueRi[nameTongJi]
#     countWeiJieJueYi = countWeiJieJueYi + listWeiJieJueYi[nameTongJi]
#     countManYiRi = countManYiRi + listManYiRi[nameTongJi]
#     countManYiYi = countManYiYi + listManYiYi[nameTongJi]
#     countJiBenRi = countJiBenRi + listJiBenRi[nameTongJi]
#     countJiBenYi = countJiBenYi + listJiBenYi[nameTongJi]
#     countBuManYiRi = countBuManYiRi + listBuManYiRi[nameTongJi]
#     countBuManYiYi = countBuManYiYi + listBuManYiYi[nameTongJi]
#     countKaoHeOther = countKaoHeOther + listKaoHeOther[nameTongJi]
#     countLianXiOther = countLianXiOther + listLianXiOther[nameTongJi]
#     countWeiLianXiOther = countWeiLianXiOther + listWeiLianXiOther[nameTongJi]
#     countKaoHeOtherKeShi = countKaoHeOtherKeShi + listKaoHeOther[nameTongJi]
#
#
# for indexTongJi in range(0, len(listSheQu)):
#     nameTongJi = listSheQu[indexTongJi]
#     countJieDanRi = countJieDanRi + listJieDanRi[nameTongJi]
#     countJieDanYi = countJieDanYi + listJieDanYi[nameTongJi]
#     countKaoHeRi = countKaoHeRi + listKaoHeRi[nameTongJi]
#     countKaoHeRiSheQu = countKaoHeRiSheQu + listKaoHeRi[nameTongJi]
#     countKaoHeYi = countKaoHeYi + listKaoHeYi[nameTongJi]
#     countKaoHeYiSheQu = countKaoHeYiSheQu + listKaoHeYi[nameTongJi]
#     countLianXiRi = countLianXiRi + listLianXiRi[nameTongJi]
#     countLianXiYi = countLianXiYi + listLianXiYi[nameTongJi]
#     countWeiLianXiRi = countWeiLianXiRi + listWeiLianXiRi[nameTongJi]
#     countWeiLianXiYi = countWeiLianXiYi + listWeiLianXiYi[nameTongJi]
#     countJieJueRi = countJieJueRi + listJieJueRi[nameTongJi]
#     countJieJueYi = countJieJueYi + listJieJueYi[nameTongJi]
#     countWeiJieJueRi = countWeiJieJueRi + listWeiJieJueRi[nameTongJi]
#     countWeiJieJueYi = countWeiJieJueYi + listWeiJieJueYi[nameTongJi]
#     countManYiRi = countManYiRi + listManYiRi[nameTongJi]
#     countManYiYi = countManYiYi + listManYiYi[nameTongJi]
#     countJiBenRi = countJiBenRi + listJiBenRi[nameTongJi]
#     countJiBenYi = countJiBenYi + listJiBenYi[nameTongJi]
#     countBuManYiRi = countBuManYiRi + listBuManYiRi[nameTongJi]
#     countBuManYiYi = countBuManYiYi + listBuManYiYi[nameTongJi]
#     countKaoHeOther = countKaoHeOther + listKaoHeOther[nameTongJi]
#     countLianXiOther = countLianXiOther + listLianXiOther[nameTongJi]
#     countWeiLianXiOther = countWeiLianXiOther + listWeiLianXiOther[nameTongJi]
#     countKaoHeOtherSheQu = countKaoHeOtherSheQu + listKaoHeOther[nameTongJi]
#
# logger.debug('只考核响应率-疫情工单-考核：{}'.format(countKaoHeYi))
# logger.debug('只考核响应率-疫情工单-联系：{}'.format(countLianXiYi))
# logger.debug('只考核响应率-疫情工单-未联系：{}'.format(countWeiLianXiYi))
#
# logger.debug('只考核响应率-其他工单-考核：{}'.format(countKaoHeOther))
# logger.debug('只考核响应率-其他工单-联系：{}'.format(countLianXiOther))
# logger.debug('只考核响应率-其他工单-未联系：{}'.format(countWeiLianXiOther))
#
# logger.debug('考核-日常：{}'.format(countKaoHeRi))
# logger.debug('联系-日常：{}'.format(countLianXiRi))
# logger.debug('未联系-日常：{}'.format(countWeiLianXiRi))
# logger.debug('解决-日常：{}'.format(countJieJueRi))
# logger.debug('未解决-日常：{}'.format(countWeiJieJueRi))
# logger.debug('满意-日常：{}'.format(countManYiRi))
# logger.debug('基本满意-日常：{}'.format(countJiBenRi))
# logger.debug('不满意-日常：{}'.format(countBuManYiRi))
#
#
# def make_lv_ping_jun(lv_ri_chang, lv_yi_qing, name_bu_men, lv_list, zhi_only, lv_other=None):
#     if zhi_only:
#         lv_list[name_bu_men] = lv_ri_chang
#     else:
#         numCount = 0
#         sumCount = 0
#         if lv_yi_qing is not None:
#             sumCount = sumCount + lv_yi_qing
#             numCount = numCount + 1
#         if lv_ri_chang is not None:
#             sumCount = sumCount + lv_ri_chang
#             numCount = numCount + 1
#         if lv_other is not None:
#             sumCount = sumCount + lv_other
#             numCount = numCount + 1
#         if sumCount == 1:
#             lv_list[name_bu_men] = sumCount
#         elif sumCount > 0:
#             lv_list[name_bu_men] = round(sumCount / numCount, 2)
#
#
# def make_de_fen_info(name_bu_men):
#     kaoHeCount = listKaoHeRi[name_bu_men] + listKaoHeYi[name_bu_men] + listKaoHeOther[name_bu_men]
#     if kaoHeCount != 0:
#         if listLianXiRi[name_bu_men] + listLianXiYi[name_bu_men] + listLianXiOther[name_bu_men] == kaoHeCount:
#             deFenLianXi[name_bu_men] = 5
#         else:
#             deFenLianXi[name_bu_men] = 0
#         if listKaoHeRi[name_bu_men] == 0:
#             deFenJieJue[name_bu_men] = 20
#             deFenManYi[name_bu_men] = 15
#         else:
#             deFenJieJue[name_bu_men] = round(listJieJueRi[name_bu_men] / listKaoHeRi[name_bu_men] * 20, 2)
#             deFenManYi[name_bu_men] = round((listManYiRi[name_bu_men]
#                                              + listJiBenRi[name_bu_men])/listKaoHeRi[name_bu_men] * 15, 2)
#     else:
#         deFenLianXi[name_bu_men] = 5
#         deFenJieJue[name_bu_men] = 20
#         deFenManYi[name_bu_men] = 15
#     deFenFinal[name_bu_men] = deFenLianXi[name_bu_men] + deFenJieJue[name_bu_men] + deFenManYi[name_bu_men]
#     if name_bu_men in listSheQu:
#         jieDanCount = listJieDanRi[name_bu_men] + listJieDanYi[name_bu_men]
#         if jieDanCount == 0:
#             deFenGong[name_bu_men] = 10
#         else:
#             deFenGong[name_bu_men] = 5 - (jieDanCount - listQianRenSuQiu[name_bu_men]) * 0.2
#         if deFenGong[name_bu_men] < 0:
#             deFenGong[name_bu_men] = 0
#         if deFenGong[name_bu_men] > 10:
#             deFenGong[name_bu_men] = 10
#         deFenFinal[name_bu_men] = deFenFinal[name_bu_men] + deFenGong[name_bu_men]
#
#
# def make_pai_ming_shu_ju(list_src):
#     for rowIndex in range(0, len(list_src)):
#         buMenName = list_src[rowIndex]
#         if listKaoHeRi[buMenName] != 0:
#             lvLianXiRi[buMenName] = round(listLianXiRi[buMenName]/listKaoHeRi[buMenName]*100, 2)
#             lvWeiLianXiRi[buMenName] = round(listWeiLianXiRi[buMenName]/listKaoHeRi[buMenName]*100, 2)
#             lvJieJueRi[buMenName] = round(listJieJueRi[buMenName] / listKaoHeRi[buMenName]*100, 2)
#             lvWeiJieJueRi[buMenName] = round(listWeiJieJueRi[buMenName] / listKaoHeRi[buMenName] * 100, 2)
#             lvManYiRi[buMenName] = round(listManYiRi[buMenName] / listKaoHeRi[buMenName] * 100, 2)
#             lvJiBenRi[buMenName] = round(listJiBenRi[buMenName] / listKaoHeRi[buMenName]*100, 2)
#             lvBuManYiRi[buMenName] = round(listBuManYiRi[buMenName] / listKaoHeRi[buMenName] * 100, 2)
#             lvManYiFinalRi[buMenName] = round((listManYiRi[buMenName] + listJiBenRi[buMenName])
#                                               / listKaoHeRi[buMenName] * 100, 2)
#         if listKaoHeYi[buMenName] != 0:
#             lvLianXiYi[buMenName] = round(listLianXiYi[buMenName]/listKaoHeYi[buMenName]*100, 2)
#         if listKaoHeOther[buMenName] != 0:
#             lvLianXiOther[buMenName] = round(listLianXiOther[buMenName]/listKaoHeOther[buMenName]*100, 2)
#
#         make_lv_ping_jun(lvLianXiRi[buMenName], lvLianXiYi[buMenName], buMenName
#                          , lvLianXiAverage, False, lvLianXiOther[buMenName])
#         make_lv_ping_jun(lvJieJueRi[buMenName], lvJieJueYi[buMenName], buMenName, lvJieJueAverage, True)
#         make_lv_ping_jun(lvManYiRi[buMenName], lvManYiYi[buMenName], buMenName, lvManYiAverage, True)
#         make_lv_ping_jun(lvJiBenRi[buMenName], lvJiBenYi[buMenName], buMenName, lvJiBenAverage, True)
#         make_lv_ping_jun(lvBuManYiRi[buMenName], lvBuManYiYi[buMenName], buMenName, lvBuManYiAverage, True)
#         make_lv_ping_jun(lvManYiFinalRi[buMenName], lvManYiFinalYi[buMenName], buMenName, lvManYiFinalAverage, True)
#
#         # 联系率计算平均值
#         make_de_fen_info(buMenName)
#
#         logger.debug('{} 日常 考核：{}  联系：{} 联系率：{} 未联系：{} 未联系率：{} 解决：{} 解决率：{} 未解决：{} 未解决率：{} '
#                      '双满：{} 双满率：{} 基本满意：{} 基本满意率：{} 不满意：{} 不满意率：{} 满意率：{}'
#                      .format(buMenName, listKaoHeRi[buMenName], listLianXiRi[buMenName], lvLianXiRi[buMenName]
#                              , listWeiLianXiRi[buMenName], lvWeiLianXiRi[buMenName], listJieJueRi[buMenName]
#                              , lvJieJueRi[buMenName], listWeiJieJueRi[buMenName], lvWeiJieJueRi[buMenName]
#                              , listManYiRi[buMenName], lvManYiRi[buMenName], listJiBenRi[buMenName]
#                              , lvJiBenRi[buMenName], listBuManYiRi[buMenName], lvBuManYiRi[buMenName]
#                              , lvManYiFinalRi[buMenName]))
#         logger.debug('{} 疫情 考核：{}  联系：{} 联系率：{} 未联系：{} 未联系率：{} 解决：{} 解决率：{} 未解决：{} 未解决率：{} '
#                      '双满：{} 双满率：{} 基本满意：{} 基本满意率：{} 不满意：{} 不满意率：{} 满意率：{}'
#                      .format(buMenName, listKaoHeYi[buMenName], listLianXiYi[buMenName], lvLianXiYi[buMenName]
#                              , listWeiLianXiYi[buMenName], lvWeiLianXiYi[buMenName], listJieJueYi[buMenName]
#                              , lvJieJueYi[buMenName], listWeiJieJueYi[buMenName], lvWeiJieJueYi[buMenName]
#                              , listManYiYi[buMenName], lvManYiYi[buMenName], listJiBenYi[buMenName]
#                              , lvJiBenYi[buMenName], listBuManYiYi[buMenName], lvBuManYiYi[buMenName]
#                              , lvManYiFinalYi[buMenName]))
#         logger.debug('{} 平均  联系率：{} 未联系率：{} 解决率：{} 未解决率：{} 双满率：{} 基本满意率：{} 不满意率：{} 满意率：{}'
#                      .format(buMenName, lvLianXiAverage[buMenName], lvWeiLianXiAverage[buMenName]
#                              , lvJieJueAverage[buMenName], lvWeiJieJueAverage[buMenName], lvManYiAverage[buMenName]
#                              , lvJiBenAverage[buMenName], lvBuManYiAverage[buMenName], lvManYiFinalAverage[buMenName]))
#
#
# make_pai_ming_shu_ju(listKeShi)
# make_pai_ming_shu_ju(listSheQu)
#
# # '拱辰街道科室诉求总量反映情况统计（2022年10月19日0时至10月31日9时）'
# strTitleKeShi = '{}月考核期拱辰街道科室接诉即办排名情况\r\n（'.format(numMonth)
# strTitleKeShi = strTitleKeShi + '{}年{}月19日0时至'.format(timeCur.year, numMonth-1)
# strTitleKeShi = strTitleKeShi + '{}月18日24时)'.format(numMonth)
#
# # 拱辰街道社区、村诉求考核反映情况统计（2022年9月19日0时至10月8日12时）
# strTitleSheQu = '{}月考核期拱辰街道社区、村接诉即办排名情况\r\n（'.format(numMonth)
# strTitleSheQu = strTitleSheQu + '{}年{}月19日0时至'.format(timeCur.year, numMonth-1)
# strTitleSheQu = strTitleSheQu + '{}月18日24时)'.format(numMonth)
#
# init_excel_title(sheetKeShi, strTitleKeShi)
# init_excel_title(sheetSheQu, strTitleSheQu, 1)
#
# strMain = '考核方法：\r\n1.工单里考核，仅考虑接单量，不考虑考核量，例如：接单量为0，考核量不为0时，算作零诉求社区，加5分'
# strMain = strMain + '\r\n2.计算满意率时，基本满意算作满意工单'
#
#
# def insert_into_sheet(sheet_insert, inset_bu_men, de_fen, is_she_qu=0):
#     rowCount = sheet_insert.max_row
#     rowInsert = rowCount + 1
#     if de_fen is not None:
#         for rowPai in range(4, rowCount+1):
#             deFenRow = sheet_insert.cell(rowPai, colDeFen).value
#             if deFenRow is not None:
#                 if de_fen >= deFenRow:
#                     sheet_insert.insert_rows(rowPai)
#                     rowInsert = rowPai
#                     break
#             else:
#                 sheet_insert.insert_rows(rowPai)
#                 rowInsert = rowPai
#                 break
#     sheet_insert.cell(rowInsert, 2).value = inset_bu_men            # 接单部门
#     logger.debug('{} 接单量：{}  疫情：{}   '
#                  '日常：{} '.format(inset_bu_men, listJieDanRi[inset_bu_men] + listJieDanYi[inset_bu_men]
#                                    , listJieDanYi[inset_bu_men], listJieJueRi[inset_bu_men]))
#     if listJieDanRi[inset_bu_men] + listJieDanYi[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colJieDanInfo+1).value = listJieDanRi[inset_bu_men] + listJieDanYi[inset_bu_men]  # 接单总量
#     if listJieDanYi[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colJieDanInfo+2).value = listJieDanYi[inset_bu_men]  # 接单总量(件)-疫情
#     if listJieDanRi[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colJieDanInfo+3).value = listJieDanRi[inset_bu_men]  # 接单总量(件)-日常
#     if listKaoHeRi[inset_bu_men] + listKaoHeYi[inset_bu_men] + listKaoHeOther[inset_bu_men] != 0:
#         countAll = listKaoHeRi[inset_bu_men] + listKaoHeYi[inset_bu_men] + listKaoHeOther[inset_bu_men]  # 纳入考核(件)-总量
#         sheet_insert.cell(rowInsert, colCountKaoHe).value = countAll
#     # 只考核响应率-疫情工单
#     if listKaoHeYi[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colYiKaoHe).value = listKaoHeYi[inset_bu_men]  # 只考核响应率-疫情工单-考核
#     if listLianXiYi[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colYiLian).value = listLianXiYi[inset_bu_men]  # 只考核响应率-疫情工单-联系
#     if listWeiLianXiYi[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colYiLianNo).value = listWeiLianXiYi[inset_bu_men]  # 只考核响应率-疫情工单-未联系
#     if lvLianXiYi[inset_bu_men] is not None:
#         sheet_insert.cell(rowInsert, colYiLvLian).value = lvLianXiYi[inset_bu_men]  # 只考核响应率-疫情工单-响应率
#     # 只考核响应率-其他工单
#     if listKaoHeOther[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colOtherKaoHe).value = listKaoHeOther[inset_bu_men]  # 只考核响应率-其他工单-考核
#     if listLianXiOther[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colOtherLian).value = listLianXiOther[inset_bu_men]  # 只考核响应率-其他工单-联系
#     if listWeiLianXiOther[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colOtherLianNo).value = listWeiLianXiOther[inset_bu_men]  # 只考核响应率-其他工单-未联系
#     if lvLianXiOther[inset_bu_men] is not None:
#         sheet_insert.cell(rowInsert, colOtherLvLian).value = lvLianXiOther[inset_bu_men]  # 只考核响应率-其他工单-响应率
#
#     # 日常工单
#     if listKaoHeRi[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colRiKaoHe).value = listKaoHeRi[inset_bu_men]  # 日常-考核
#     if listLianXiRi[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colRiLian).value = listLianXiRi[inset_bu_men]  # 日常-联系
#     if listWeiLianXiRi[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colRiLianNo).value = listWeiLianXiRi[inset_bu_men]  # 日常-未联系
#     if listJieJueRi[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colRiJie).value = listJieJueRi[inset_bu_men]  # 日常-解决
#     if listWeiJieJueRi[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colRiJieNo).value = listWeiJieJueRi[inset_bu_men]  # 日常-未解决
#     if listManYiRi[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colRiMan).value = listManYiRi[inset_bu_men]  # 日常-满意
#     if listJiBenRi[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colRiJiBen).value = listJiBenRi[inset_bu_men]  # 日常-基本满意
#     if listBuManYiRi[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colRiManNo).value = listBuManYiRi[inset_bu_men]  # 日常-不满意
#     if lvLianXiRi[inset_bu_men] is not None:
#         sheet_insert.cell(rowInsert, colRilvLian).value = lvLianXiRi[inset_bu_men]  # 日常-响应率
#     if lvJieJueRi[inset_bu_men] is not None:
#         sheet_insert.cell(rowInsert, colRiLvJie).value = lvJieJueRi[inset_bu_men]  # 日常-解决率
#     if lvManYiFinalRi[inset_bu_men] is not None:
#         sheet_insert.cell(rowInsert, colRiLvMan).value = lvManYiFinalRi[inset_bu_men]  # 日常-满意率
#
#     # 平均响应率 平均解决率 平均满意率
#     if lvLianXiAverage[inset_bu_men] is not None:
#         sheet_insert.cell(rowInsert, colAvgLian).value = lvLianXiAverage[inset_bu_men]  # 平均响应率
#     if lvJieJueAverage[inset_bu_men] is not None:
#         sheet_insert.cell(rowInsert, colAvgJie).value = lvJieJueAverage[inset_bu_men]  # 平均解决率
#     if lvManYiFinalAverage[inset_bu_men] is not None:
#         sheet_insert.cell(rowInsert, colAvgMan).value = lvManYiFinalAverage[inset_bu_men]  # 平均满意率
#
#     # 得分情况
#     if deFenLianXi[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colDeFenLian).value = deFenLianXi[inset_bu_men]  # 响应率得分
#     if deFenJieJue[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colDeFenJie).value = deFenJieJue[inset_bu_men]  # 解决率得分
#     if deFenManYi[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colDeFenMan).value = deFenManYi[inset_bu_men]  # 满意率得分
#     if deFenGong[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colDeFenGong).value = deFenGong[inset_bu_men]  # 工单里得分
#     if deFenCai[inset_bu_men] != 0:
#         sheet_insert.cell(rowInsert, colDeFenCai).value = deFenCai[inset_bu_men]  # 材料报送得分
#     sheet_insert.cell(rowInsert, colDeFen).value = de_fen  # 得分
#     if is_she_qu == 1:
#         sheet_insert.cell(rowInsert, colSuoShuPianQu).value = listSuoShuPianQu[inset_bu_men]  # 所属片区
#         sheet_insert.cell(rowInsert, colFenDang).value = listFenDang[inset_bu_men]  # 分档
#         if listQianRenSuQiu[inset_bu_men] != 0:
#             sheet_insert.cell(rowInsert, colJieDanInfo).value = listQianRenSuQiu[inset_bu_men]  # 接单情况-千人诉求量
#
#
#
# def make_pai_ming(sheet_make, list_make, is_she_qu=0):
#     for indexName in range(0, len(list_make)):
#         nameIndex = list_make[indexName]
#         insert_into_sheet(sheet_make, nameIndex, deFenFinal[nameIndex], is_she_qu)
#
#
# make_pai_ming(sheetKeShi, listKeShi)
# make_pai_ming(sheetSheQu, listSheQu, 1)
#
#
# def inset_he_ji_bei_zhu(sheet_he_ji, str_bei_zhu, is_she_qu=0):
#     rowCount = sheet_he_ji.max_row
#     colCount = sheet_he_ji.max_column
#     numLast = 0
#     colDeFenHeJi = get_col_index(sheet_he_ji, 2, '得分')
#     colPaiMingHeJi = get_col_index(sheet_he_ji, 2, '排名')
#     for rowHeJi in range(4, rowCount+1):
#         sheet_he_ji.cell(rowHeJi, 1).value = rowHeJi-3
#         if rowHeJi != 4:
#             valueDeFen = sheet_he_ji.cell(rowHeJi, colDeFenHeJi).value
#             if valueDeFen is not None:
#                 if valueDeFen == numLast:
#                     sheet_he_ji.cell(rowHeJi, colPaiMingHeJi).value = sheet_he_ji.cell(rowHeJi-1, colPaiMingHeJi).value
#                 else:
#                     sheet_he_ji.cell(rowHeJi, colPaiMingHeJi).value = sheet_he_ji.cell(rowHeJi, 1).value
#                 numLast = valueDeFen
#         else:
#             sheet_he_ji.cell(rowHeJi, colPaiMingHeJi).value = 1
#             numLast = sheet_he_ji.cell(rowHeJi, colDeFenHeJi).value
#     # get_column_letter(rowCount+1)
#     strHeJi = 'A{}:B{}'.format(rowCount+1, rowCount+1)
#     sheet_he_ji.merge_cells(range_string=strHeJi)
#     sheet_he_ji.cell(rowCount+1, 1).value = '合计'
#     if is_she_qu == 1:
#         sheet_he_ji.merge_cells(range_string='A{}:AJ{}'.format(rowCount + 2, rowCount + 2))
#         # strHeJi2 = 'A{}:AJ{}'.format(rowCount + 2, rowCount + 2)
#     else:
#         sheet_he_ji.merge_cells(range_string='A{}:AI{}'.format(rowCount + 2, rowCount + 2))
#         # strHeJi2 = 'A{}:AI{}'.format(rowCount + 2, rowCount + 2)
#     # sheet_he_ji.merge_cells(range_string=strHeJi2)
#     sheet_he_ji.cell(rowCount + 2, 1).value = str_bei_zhu
#     sheet_he_ji.row_dimensions[rowCount + 2].height = 50
#     for colHeJi in range(3, colCount+1):
#         valueColCell = sheet_he_ji.cell(3, colHeJi).value
#         colAvai = 0
#         if valueColCell is not None:
#             if valueColCell.find('率') == -1:
#                 colAvai = colHeJi
#         else:
#             valueColCell2 = sheet_he_ji.cell(2, colHeJi).value
#             if valueColCell2 is not None:
#                 if valueColCell2 == '考核总量' or valueColCell2 == '裸分得分' or valueColCell2 == '裸分失分':
#                     colAvai = colHeJi
#         if colAvai != 0:
#             key = sheet_he_ji.cell(rowCount + 1, colAvai).coordinate
#             # 求和的开始单元格地址
#             start = sheet_he_ji.cell(4, colAvai).coordinate
#             # 求和的结束单元格地址
#             end = sheet_he_ji.cell(rowCount, colAvai).coordinate
#             sheet_he_ji[key] = f'=SUM({start}:{end})'
#
#
# strMain = '备注：' + strMain
# inset_he_ji_bei_zhu(sheetKeShi, strMain)
# inset_he_ji_bei_zhu(sheetSheQu, strMain)
# # def inset_into_sheet_in_line(line)
# bookKaoHe.save(fileResultSave)


